# 加载数据库连接驱动
import pymysql

#数据库连接信息
db = pymysql.connect(host='128.26.20.68',
                     user='lidong',
                     password='123456',
                     database='world')
# 创建游标对象
cursor = db.cursor()

res = {
    "info": [
        {
            "name": "张三",
            "level": 1,
            "sex": "女",
            "fight_power": 0.00001
        },
        {
            "name": "李四",
            "level": 100,
            "sex": "男",
            "fight_power": 1000000
        }
    ],
    "backup1": "备用字段1",
    "backup2": "备用字段2"
}


# 执行SQL查询
cursor.execute("DROP TABLE IF EXISTS user_info")

sql_createtable = """CREATE TABLE user_info (
                     NAME  CHAR(20) NOT NULL,
                     LEVEL INT,
                     SEX CHAR(1),
                     Fighting_power FLOAT )"""
cursor.execute(sql_createtable)

i = 0
while True:
    try:
        sql_insert = "INSERT INTO user_info(NAME,LEVEL,SEX, Fighting_power) VALUES ('%s', %s, '%s', %s)" \
                      %(res['info'][i]['name'], res['info'][i]['level'], res['info'][i]['sex'], res['info'][i]['fight_power'])
        print(sql_insert)
        cursor.execute(sql_insert)
        db.commit()
        i += 1
    except:
        db.rollback()
        break

db.close()